package com.baolai.mapper;

import com.baolai.dto.GoodsShelf;
import com.baolai.entity.ShelfGoods;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ShelfGoodsMapper extends BaseMapper<ShelfGoods> {

    @Select("select shelf_goods.goods_id as goods_id,goods.name as goods_name,goods.type as goods_type," +
            "goods.valuation as goods_value,shelf_goods.site as site,shelf_goods.number as number " +
            "from shelf_goods " +
            "left join goods on shelf_goods.goods_id = goods.id " +
            "where shelf_goods.goods_id = '${goodsId}' and shelf_goods.site = '${site}'")
    GoodsShelf getShelfGoods(String goodsId,String site);

    @Select("select top ${size} * from (" +
            "select *,row_number() over(order by site asc) row from (" +
            "select shelf_goods.goods_id as goods_id,goods.name as goods_name,goods.type as goods_type," +
            "goods.valuation as goods_value,shelf_goods.site as site,shelf_goods.number as number " +
            "from shelf_goods " +
            "left join goods on shelf_goods.goods_id = goods.id) t1) t2 " +
            "where t2.row > ${current}")
    List<GoodsShelf> listShelfGoods(Integer current, Integer size);

    @Select("select top ${size} * from (" +
            "select *,row_number() over(order by site asc) row from (" +
            "select shelf_goods.goods_id as goods_id,goods.name as goods_name,goods.type as goods_type," +
            "goods.valuation as goods_value,shelf_goods.site as site,shelf_goods.number as number " +
            "from shelf_goods " +
            "left join goods on shelf_goods.goods_id = goods.id " +
            "where goods.name like '${key}%') t1) t2 " +
            "where t2.row > ${current}")
    List<GoodsShelf> queryGoodsShelf(String key,Integer current,Integer size);

    @Select("select top ${size} * from (" +
            "select *,row_number() over(order by site asc) row from (" +
            "select shelf_goods.goods_id as goods_id,goods.name as goods_name,goods.type as goods_type," +
            "goods.valuation as goods_value,shelf_goods.site as site,shelf_goods.number as number " +
            "from shelf_goods " +
            "left join goods on (shelf_goods.goods_id = goods.id) " +
            "where shelf_goods.site like '${key}%') t1) t2 " +
            "where t2.row > ${current}")
    List<GoodsShelf> queryGoodsShelfBySite(String key,Integer current,Integer size);

    @Select("select count(*) " +
            "from shelf_goods " +
            "left join goods on (shelf_goods.goods_id = goods.id) " +
            "where goods.name like '${key}%'")
    Integer countGoodsShelf(String key);
}
